import os
import pymysql
from datetime import datetime, timedelta
import schedule
import time

# 数据库配置
db_config = {
    'host': 'localhost',
    'user': 'ry_online',
    'password': '123456',
    'database': 'ryerp'
}

# 备份文件夹
backup_dir = 'C:\\Users\liudy\mysqlDbDump'

# 确保备份目录存在
if not os.path.exists(backup_dir):
    os.makedirs(backup_dir)


def export_to_sql():
    """导出 MySQL 数据到 SQL 文件"""
    try:
        # 连接数据库
        connection = pymysql.connect(**db_config)
        cursor = connection.cursor()

        # 获取所有表名
        cursor.execute("SHOW TABLES")
        tables = cursor.fetchall()

        # 生成备份文件名
        timestamp = datetime.now().strftime('%Y-%m-%d_%H-%M-%S')
        sql_file_path = os.path.join(backup_dir, f"backup_{timestamp}.sql")

        with open(sql_file_path, 'w', encoding='utf-8') as sql_file:
            for table_name, in tables:
                # 获取表创建语句
                cursor.execute(f"SHOW CREATE TABLE `{table_name}`")
                create_table_sql = cursor.fetchone()[1]
                sql_file.write(f"-- Table structure for `{table_name}`\n")
                sql_file.write(f"{create_table_sql};\n\n")

                # 如果是 sys_oper_log 表，跳过数据导出
                if table_name == 'sys_oper_log':
                    sql_file.write(f"-- 跳过 sys_oper_log 表的数据导出（仅保留结构）\n\n")
                    continue  # 跳过数据导出部分

                # 导出其他表的数据
                cursor.execute(f"SELECT * FROM `{table_name}`")
                rows = cursor.fetchall()
                if rows:
                    sql_file.write(f"-- Data for `{table_name}`\n")
                    for row in rows:
                        # 格式化插入语句
                        values = ', '.join(
                            ["'{}'".format(str(value).replace("'", "\\'")) if value is not None else 'NULL' for value in
                             row])
                        sql_file.write("INSERT INTO `{}` VALUES ({});\n".format(table_name, values))
                    sql_file.write("\n")

        print(f"数据已成功导出到 {sql_file_path}")
    except Exception as e:
        print(f"导出失败：{e}")
    finally:
        if connection:
            connection.close()


def clean_old_backups():
    """删除超过 3 天的备份文件"""
    try:
        # 当前时间
        now = datetime.now()

        for file in os.listdir(backup_dir):
            file_path = os.path.join(backup_dir, file)
            # 检查是否为文件并且文件名包含日期格式
            if os.path.isfile(file_path) and file.startswith("backup_"):
                # 提取文件日期
                file_date_str = file.split("_")[1].split(".")[0]
                file_date = datetime.strptime(file_date_str, '%Y-%m-%d')

                # 如果文件超过 3 天，则删除
                if now - file_date > timedelta(days=3):
                    os.remove(file_path)
                    print(f"删除过期备份文件：{file_path}")
    except Exception as e:
        print(f"清理失败：{e}")


def job():
    """每日任务"""
    export_to_sql()
    clean_old_backups()


# 每日凌晨 3 点运行任务
schedule.every().day.at("02:00").do(job)

print("脚本正在运行，等待每日任务执行...")
while True:
    schedule.run_pending()
    time.sleep(1)